﻿use [ASIVesteStore]

go

/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('decreaseProductStock') is not null)
	DROP PROCEDURE decreaseProductStock
	
go

create procedure decreaseProductStock(@productCode int, @amount int)
as
	begin transaction set transaction isolation level repeatable read

	if(@amount <= (select stock from Product where code = @productCode))
	begin

		update Product set stock = stock - @amount where code = @productCode

		select 1
	end
	
	select 0
	commit 
go


/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('productStock') is not null)
	DROP PROCEDURE productStock
	
go

create procedure productStock(@productCode int)
as
	select stock from Product where code = @productCode



go


/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('getProducts') is not null)
	DROP PROCEDURE getProducts
	
go

create procedure getProducts(@page int, @recordsPerPage int)
as

	declare @firstrow int
	declare @lastrow int

	set @firstrow = ( @page - 1) * @recordsPerPage + 1
	set @lastrow = (@page - 1) * @recordsPerPage + @recordsPerPage
	
	select code, stock, type, designation, price
	from(
		select code, stock, type, designation, price,
			   ROW_NUMBER() over(order by code) as RowNumber
		from Product
	) as t
	where RowNumber between  @firstrow and @lastrow


go

/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('getNumberOfProducts') is not null)
	DROP PROCEDURE getNumberOfProducts
	
go

create procedure getNumberOfProducts
as
	select COUNT(*) from Product



go


/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('addStock') is not null)
	DROP PROCEDURE addStock
	
go

create procedure addStock(@productCode int, @amount int)
as
	update Product set stock = stock + @amount where code = @productCode


go

/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('addProduct') is not null)
	DROP PROCEDURE addProduct
	
go

create procedure addProduct(@productCode int, @designation varchar(200), @price money, @stock int, @type varchar(20))
as
	insert into Product(code, stock, type, designation, price) 
	values(@productCode, @stock, @type, @designation, @price)


go

/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('getProduct') is not null)
	DROP PROCEDURE getProduct
	
go

create procedure getProduct(@productCode int)
as
	select code, stock, type, designation, price
	from Product
	where code = @productCode


go